package com.example.javagd.util;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.nio.channels.FileChannel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import lombok.Data;

/**
 * @program: javagd
 * @description:
 * @author: hzy
 * @create: 2021-04-09 14:28
 **/
public class InitTableUtils {

    @Data
    static class TableMsg{
        String tableName;
        List<FieldMsg> FieldMsgList;

        public TableMsg(String tableName, List<FieldMsg> FieldMsgList){
            this.tableName = tableName;
            this.FieldMsgList = FieldMsgList;
        }
    }

    @Data
    static class FieldMsg{
        boolean isKey;
        String tableField;
        String FieldType;
        String property;
        boolean isChanged;
        String oldField;

        public FieldMsg(boolean isKey, String tableField, String FieldType, String property, boolean isChanged, String oldField){
            this.isKey = isKey;
            this.tableField = tableField;
            this.FieldType = FieldType;
            this.property = property;
            this.isChanged = isChanged;
            this.oldField = oldField;
        }
    }

    private static String pojoPath = "src/main/java/com/example/javagd/pojo/";
    private static String xmlPath = "src/main/resources/mappers/";
    private static Map<String, TableMsg> map = new HashMap<>();

    private static List<FieldMsg> list = new ArrayList<>();
    static boolean table_auto_update = true;

    public static void Init(){
        if (table_auto_update){
            getAllTableMsg();
            changeXml();
            updateTable();
        }
    }

    static String database = "template";

    static String driver = "com.mysql.jdbc.Driver";

    static String url = "jdbc:mysql://localhost:3306/" + database + "?useSSL=false&characterEncoding=utf8";

    static String user = "root";

    static String password = "123456";

    static String basePojoFile = "BasePojo.java";


    private static void updateTable(){

        Connection conn = null;
        Statement stmt = null;

        try{

            // 注册 JDBC 驱动
            Class.forName(driver);
            // 打开链接
            conn = DriverManager.getConnection(url, user, password);

            //从map取出字段和properties
            for(Map.Entry<String, TableMsg> entry : map.entrySet()){
                TableMsg mapValue = entry.getValue();
                boolean exist = false;
                ResultSet rs = conn.getMetaData().getTables(null, null,  mapValue.getTableName(), null);
                while (rs.next()) {
                    exist = true;
                }
                rs.close();

                //查询表是否存在，不存在就创建
                if (!exist){
                    String sql = "CREATE TABLE " + mapValue.getTableName() + "(";
                    String keyField = "";
                    for (FieldMsg FieldMsg : mapValue.getFieldMsgList()){
                        if (FieldMsg.isKey())
                            keyField = FieldMsg.getTableField();

                        sql += FieldMsg.getTableField();
                        sql += " ";
                        sql += FieldMsg.getFieldType();
                        sql += ",";
                    }
                    sql += "PRIMARY KEY (";
                    sql += keyField;
                    sql += ")";
                    sql += ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

                    stmt = conn.prepareStatement("");
                    stmt.executeUpdate(sql);
                }else{
                    //存在的话就进行新增和删除字段
                    //新增更新字段
                    //查询出所有字段名

                    //查出主键是谁
                    String selectKey = "SELECT\n" +
                            "\tk.column_name\n" +
                            "FROM\n" + "\tinformation_schema.table_constraints t\n" +
                            "\tJOIN information_schema.key_column_usage k USING ( constraint_name, table_schema, table_name ) \n" +
                            "WHERE\n" +
                            "\tt.constraint_type = 'PRIMARY KEY' \n" +
                            "\tAND k.table_name = '" + mapValue.getTableName() + "' \n" +
                            "\tAND t.table_schema = '" + database + "';";
                    stmt = conn.prepareStatement("");
                    ResultSet rsKey = stmt.executeQuery(selectKey);
                    String oldKey = "";
                    while (rsKey.next()){
                        oldKey = rsKey.getString(1);
                    }




                    String sql = "select column_name from information_schema.columns where table_schema=\'"+ database +"\' and table_name=";
                    sql += "\'" + mapValue.getTableName() + "\'";
                    stmt = conn.prepareStatement("");
                    ResultSet rsField = stmt.executeQuery(sql);
                    Set<String> set = new HashSet<>();
                    List<String> tableFieldList = new ArrayList<>();
                    while (rsField.next()){
                        set.add(rsField.getString(1));
                        tableFieldList.add(rsField.getString(1));
                    }

                    //实体类的字段
                    String newKey = "";
                    List<String> pojoFieldList = new ArrayList<>();
                    for (FieldMsg fieldMsg : mapValue.getFieldMsgList()){
                        set.add(fieldMsg.getTableField());
                        pojoFieldList.add(fieldMsg.getTableField());
                        if (fieldMsg.isKey()){
                            newKey = fieldMsg.getTableField();
                        }
                    }



                    List<String> update_sql_list = new ArrayList<>();
                    String update_sql = "Alter Table " + mapValue.getTableName() + " CHANGE ";
                    List<String> remove_set = new ArrayList<>();

                    //先从set集合中遍历出更新的字段，老字段在数据库，新字段在实体类。进行更新后删除set集合的这两个值
                    for (String str : set){
                        //如果字段在实体类里
                        if (pojoFieldList.contains(str)){
                            //就去实体类找到该字段的存储
                            for (FieldMsg fieldMsg : mapValue.getFieldMsgList()){
                                if (fieldMsg.getTableField().equals(str)){
                                    //如果字段是要修改的
                                    if (fieldMsg.isChanged()){
                                        //构造修改字段的sql
                                        String sql_u = update_sql;
                                        sql_u += fieldMsg.getOldField();
                                        sql_u += " ";
                                        sql_u += fieldMsg.getTableField();
                                        sql_u += " ";
                                        sql_u += fieldMsg.getFieldType();
                                        sql_u += ";";
                                        update_sql_list.add(sql_u);
                                        remove_set.add(fieldMsg.getOldField());
                                        remove_set.add(fieldMsg.getTableField());
                                    }
                                    break;
                                }
                            }
                        }
                    }

                    //删除新旧字段 不在后续新增删除
                    for (String remove : remove_set)
                        set.remove(remove);

                    //在实体类
                    String add_sql = "Alter Table " + mapValue.getTableName() + " Add (";
                    String del_sql = "Alter Table " + mapValue.getTableName() + " DROP ";
                    List<String> del_list = new ArrayList<>();
                    for (String str : set) {
                        if (tableFieldList.contains(str) && pojoFieldList.contains(str))
                            continue;
                        else if (tableFieldList.contains(str) && !pojoFieldList.contains(str)){
                            //在数据库 不在实体类 ，删除字段
                            del_list.add(del_sql + str + ";");
                        }else if (!tableFieldList.contains(str) && pojoFieldList.contains(str)){
                            //不在数据库 在实体类 ，新增字段
                            add_sql += str;
                            add_sql += " ";
                            for (FieldMsg FieldMsg : mapValue.getFieldMsgList()){
                                if (FieldMsg.getTableField().equals(str)){
                                    add_sql += FieldMsg.getFieldType();
                                    add_sql += ",";
                                    break;
                                }
                            }
                        }
                    }

                    for (String update_sql_cmd : update_sql_list){
                        stmt = conn.prepareStatement("");
                        stmt.executeUpdate(update_sql_cmd);
                    }

                    add_sql += ");";
                    int idx = add_sql.lastIndexOf(',');
                    if (idx > 0){
                        //里面有新增的字段
                        String str1 = add_sql.substring(0,idx);;//通过截取逗号前的字符串
                        String str2 = add_sql.substring(idx+1, add_sql.length());//截取逗号后的字符串
                        add_sql = str1 + str2;//逗号前字符串和逗后后的字符串拼接，结果是去掉逗号后的字符串
                        stmt = conn.prepareStatement("");
                        stmt.executeUpdate(add_sql);
                    }

                   for (String del_sql_cmd : del_list){
                       stmt = conn.prepareStatement("");
                       stmt.executeUpdate(del_sql_cmd);
                   }

                   //更换主键
                    if (!oldKey.equals(newKey)){
                        String dropkeysql = "alter table " + mapValue.getTableName() + " drop primary key;";
                        String addkeysql = "alter table " + mapValue.getTableName() + " add primary key("+ newKey  + ");";
                        stmt = conn.prepareStatement("");
                        stmt.executeUpdate(dropkeysql);
                        stmt = conn.prepareStatement("");
                        stmt.executeUpdate(addkeysql);
                    }

                }
            }
            //注意！！没有更新字段名！！！


            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            try{
                if(stmt!=null) stmt.close();
            }catch(SQLException se2){
            }// 什么都不做
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
    }




    private static void changeXml(){
        //<!-- template -->
        List<String> allXmls = new ArrayList<>();
        getAllFileName(xmlPath, allXmls);

        for (String xmlName : allXmls) {

            //从map取出字段和properties
            for(Map.Entry<String, TableMsg> entry : map.entrySet()){
                String mapKey = entry.getKey();
                TableMsg mapValue = entry.getValue();
                mapKey = mapKey.replace("Pojo.java", "");
                if (xmlName.startsWith(mapKey)){
                    deleteLineFromBigFile(xmlPath, xmlName, "property");
                    //<result column="key3" property="key3"/>
                    String replaceText = "<!-- replace -->\n";
                    for (FieldMsg FieldMsg : mapValue.getFieldMsgList()){
                        replaceText += "\t\t<result column=\"";
                        replaceText += FieldMsg.getTableField();
                        replaceText += "\" property=\"";
                        replaceText += FieldMsg.getProperty();
                        replaceText += "\"/>";
                        replaceText += "\n";
                    }
                    alterStringToCreateNewFile(xmlPath, xmlName, "<!-- replace -->", replaceText);
                    break;
                }
            }
        }
    }


    private static void deleteLineFromBigFile(String path, String oldFile, String key) {
        File inFile = new File(path + oldFile);
        File outFile = new File(path + "new.xml");

        BufferedReader br = null;
        String readedLine;
        BufferedWriter bw = null;
        try {
            FileWriter fw = new FileWriter(outFile);
            bw = new BufferedWriter(fw);
            if (!outFile.exists()) {
                outFile.createNewFile();
            }
            br = new BufferedReader(new FileReader(inFile));
            int idx = 0;
            while ((readedLine = br.readLine()) != null) {
                if (readedLine.contains(key)) {
                    continue;
                }
                bw.write(readedLine + "\n");
                if (idx++ == 100) {
                    bw.flush();
                    idx = 0;
                }
            }
            bw.flush();
            br.close(); //关闭流，对文件进行删除等操作需先关闭文件流操作
            bw.close();


            File file = new File(path + oldFile);
            String filePath = file.getPath();
            file.delete(); //删除源文件
            outFile.renameTo(new File(filePath)); //将新文件更名为源文件

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (br != null) {
                    br.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if (bw != null) {
                    bw.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }




    private static void alterStringToCreateNewFile(String path, String oldFile, String oldString, String newString)
    {
        try {

            BufferedReader br = new BufferedReader(
                    new InputStreamReader(
                            new FileInputStream(path + oldFile))); //创建对目标文件读取流
            File newFile = new File(path + "newfile.xml"); //创建临时文件
            if (!newFile.exists()){
                newFile.createNewFile(); //不存在则创建
            }
            //创建对临时文件输出流，并追加
            BufferedWriter bw = new BufferedWriter(
                    new OutputStreamWriter(
                            new FileOutputStream(newFile,true)));
            String string = null; //存储对目标文件读取的内容

            while ((string = br.readLine()) != null){
                //判断读取的内容是否包含原字符串
                if (string.contains(oldString)){
                    //替换读取内容中的原字符串为新字符串
                    string = new String(
                            string.replace(oldString,newString));
                    bw.write(string);
                }else {
                    bw.write(string);
                    bw.newLine(); //添加换行
                }

            }
            br.close(); //关闭流，对文件进行删除等操作需先关闭文件流操作
            bw.close();

            File file = new File(path + oldFile);
            String filePath = file.getPath();
            file.delete(); //删除源文件
            newFile.renameTo(new File(filePath)); //将新文件更名为源文件

            copyFileUsingFileChannels(new File(filePath), new File("target/classes/mappers/" + oldFile));
        } catch(Exception e){
            e.printStackTrace();
        }
    }

    private static void copyFileUsingFileChannels(File source, File dest) throws IOException {
        FileChannel inputChannel = null;
        FileChannel outputChannel = null;
        try {
            inputChannel = new FileInputStream(source).getChannel();
            outputChannel = new FileOutputStream(dest).getChannel();
            outputChannel.transferFrom(inputChannel, 0, inputChannel.size());
        } finally {
            inputChannel.close();
            outputChannel.close();
        }
    }


    private static void getAllTableMsg(){
        List<String> allFiles = new ArrayList<>();
        getAllFileName(pojoPath, allFiles);

        for (String fileName : allFiles){
            File file = new File(pojoPath + fileName);
            try {
                BufferedReader bw = new BufferedReader(new FileReader(file));
                String line = null;
                boolean existKey = false;
                while ((line = bw.readLine()) != null){

                    //跳过注释
                    if (isZhushi(line))
                        continue;

                    //通用属性存到list
                    if (fileName.equals(basePojoFile)){
                        setBaseField(line);
                    }else{
                        //其他文件对每一行进行分析 设置表名和字段。判断出现主键没
                        setTableName(fileName, line);
                        existKey |= setField(fileName, line);
                    }
                }

                //子类里面没有主键。直接添加基类属性
                //否则把通用属性的key取消
                if (map.containsKey(fileName)){
                    for (FieldMsg fieldMsg : list){
                        if (existKey){
                            fieldMsg.setKey(false);
                        }
                        map.get(fileName).getFieldMsgList().add(fieldMsg);
                    }
                }


            }catch (Exception e)
            {
                e.printStackTrace();
            }

        }
    }

    private static boolean isZhushi(String line){
        if (line.startsWith("//")){
            return true;
        }

        if (line.replace(" ", "").startsWith("//"))
            return true;

        return false;
    }

    private static void setBaseField(String line){
        if (line.contains("@ApiModelProperty")){
            String Field =  getValue(0, '"', '"', line);

            String type = "varchar(255)";
            if (line.contains("String")){
                type = "varchar(255)";
            }else if (line.contains("Long")){
                type = "bigint";
            }else if (line.contains("int")){
                type = "int";
            }else if (line.contains("LocalDateTime")){
                type = "datetime";
            }
            boolean iskey = line.contains("isKey");

            String properties = getValue(1, ' ', ';', line);

            boolean hasOldField = false;
            String value = "";
            if (line.contains("oldField")){
                hasOldField = true;

                int index = line.indexOf("oldField=");
                index += "oldField=".length();
                for (;index < line.length(); index++){
                    if (line.charAt(index) == ' ' || line.charAt(index) == ',')
                        break;
                    value += line.charAt(index);
                }

            }

            list.add(new FieldMsg(iskey, Field, type, properties, hasOldField, value));
        }
    }


    private static boolean setField(String fileName, String line){
        if (line.contains("@ApiModelProperty")){
            String Field =  getValue(0, '"', '"', line);

            String type = "varchar(255)";
            if (line.contains("String")){
                type = "varchar(255)";
            }else if (line.contains("Long")){
                type = "bigint";
            }else if (line.contains("int")){
                type = "int";
            }else if (line.contains("LocalDateTime")){
                type = "datetime";
            }
            boolean iskey = line.contains("isKey");

            String properties = getValue(1, ' ', ';', line);

            boolean hasOldField = false;
            String value = "";
            if (line.contains("oldField")){
                hasOldField = true;

                int index = line.indexOf("oldField=");
                index += "oldField=".length();
                for (;index < line.length(); index++){
                    if (line.charAt(index) == ' ' || line.charAt(index) == ',')
                        break;
                    value += line.charAt(index);
                }

            }

            //表已存在，存入字段信息
            if (map.containsKey(fileName)){
                map.get(fileName).getFieldMsgList().add(new FieldMsg(iskey, Field, type, properties, hasOldField, value));
                return iskey;
            }
        }
        return false;
    }


    private static void setTableName(String fileName, String line){
        if (line.contains("@TableName")){
            String tableName =  getValue(0, '"', '"', line);

            //不存在这个表。创建
            if (!map.containsKey(fileName)){
                map.put(fileName, new TableMsg(tableName, new ArrayList<>()));
            }

        }
    }

    private static String getValue(int type, char startc, char endc, String line){
        if (type == 0){
            int start = line.indexOf(startc);
            int end = line.lastIndexOf(endc);
            if (start >= end)
                return "";
            return line.substring(start + 1, end);
        }else if (type == 1){
            int start = line.lastIndexOf(startc);
            int end = line.lastIndexOf(endc);
            if (start >= end)
                return "";
            return line.substring(start + 1, end);
        }
        return "";
    }

    /**
     * 获取某个文件夹下的所有文件
     *
     * @param fileNameList 存放文件名称的list
     * @param path 文件夹的路径
     * @return
     */
    private static void getAllFileName(String path, List<String> fileNameList) {
        File file = new File(path);
        File[] tempList = file.listFiles();

        for (int i = 0; i < tempList.length; i++) {
            if (tempList[i].isFile()) {
                fileNameList.add(tempList[i].getName());
            }
            if (tempList[i].isDirectory()) {
                getAllFileName(tempList[i].getAbsolutePath(),fileNameList);
            }
        }
    }


}
